﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Data.SqlClient;
using System.Data.OleDb;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class ProjectRequestKPIDAL : IProjectRequestKPIDAL
    {
       
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ProjectRequestKPIDAL()
        {

        }

        public void Insert(ProjectExpenseInfo pj)
        {
            //string strSQL;

            //try
            //{
            //    dbManager.Open();
            //    dbManager.CreateParameters(4);

            //    dbManager.AddParameters(0, "@reqSeq", reqSeq);
            //    dbManager.AddParameters(1, "@exp", exp);
            //    dbManager.AddParameters(2, "@status", SystemInfo._WorkRecordStatus);
            //    dbManager.AddParameters(3, "@user", user);


            //    strSQL = "INSERT INTO BGM_REQUEST_EXPENSE(BGM_REQ_SEQ, BGM_EXP_SEQ, BGM_SOURCE_SEQ, RECORD_STATUS, CREATED_BY)"
            //        + " VALUES(@reqSeq, @exp, (select eg.bgm_source_seq from bgm_mas_expense e inner join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq where e.bgm_exp_seq = @exp), @status, @user)";

            //    return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            //}
            //catch
            //{
            //    return 0;
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
        }

        public void Update(ProjectExpenseInfo pj)
        {
            //string strSQL;

            //try
            //{
            //    dbManager.Open();
            //    dbManager.CreateParameters(9);

            //    dbManager.AddParameters(0, "@reqExpSeq", reqExpSeq);
            //    dbManager.AddParameters(1, "@reqExpDescr", reqExpDescr);
            //    dbManager.AddParameters(2, "@reqExpPrice", reqExpPrice);
            //    dbManager.AddParameters(3, "@reqExpQty", reqExpQty);
            //    dbManager.AddParameters(4, "@reqExpMtef1", reqExpMtef1);
            //    dbManager.AddParameters(5, "@reqExpMtef2", reqExpMtef2);
            //    dbManager.AddParameters(6, "@reqExpMtef3", reqExpMtef3);
            //    dbManager.AddParameters(7, "@reqExpResource", reqExpResource);
            //    dbManager.AddParameters(8, "@status", SystemInfo._WorkRecordStatus);

            //    strSQL = "UPDATE BGM_REQUEST_EXPENSE"
            //        + " SET BGM_REQ_EXP_DESCR = @reqExpDescr"
            //            + ", BGM_REQ_EXP_PRICE = @reqExpPrice"
            //            + ", BGM_REQ_EXP_QTY = @reqExpQty"
            //            + ", BGM_REQ_EXP_MTEF1 = @reqExpMtef1"
            //            + ", BGM_REQ_EXP_MTEF2 = @reqExpMtef2"
            //            + ", BGM_REQ_EXP_MTEF3 = @reqExpMtef3"
            //            + ", ICM_RESOURCE_LIST_SEQ = @reqExpResource"
            //            + ", RECORD_STATUS = @status"
            //            + ", UPDATED_WHEn = GetDate()"
            //        + " WHERE BGM_REQ_EXP_SEQ = @reqExpSeq";

            //    return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            //}
            //catch
            //{
            //    return 0;
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
        }

        public void Delete(ProjectExpenseInfo pj)
        {
            //string strSQL;

            //try
            //{
            //    dbManager.Open();
            //    dbManager.CreateParameters(3);

            //    dbManager.AddParameters(0, "@seq", seq);
            //    dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
            //    dbManager.AddParameters(2, "@updated_by", updated_by);

            //    strSQL = "UPDATE BGM_REQUEST_EXPENSE"
            //        + " SET RECORD_STATUS = @record_status"
            //            + ", UPDATED_BY = @updated_by"
            //            + ", UPDATED_WHEN = GetDate()"
            //        + " WHERE BGM_REQ_EXP_SEQ = @seq";

            //    return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            //}
            //catch
            //{
            //    return 0;
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
        }

        public IList<ProjectKPIInfo> Search(ProjectKPIInfo pj)
        {
            IList<ProjectKPIInfo> pjInfo = new List<ProjectKPIInfo>();

            string strSQL;
            int pjSeq = 0;

            if (pj.Project != null)
                pjSeq = pj.Project.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", pj.Sequence);
                dbManager.AddParameters(1, "@pjSeq", pjSeq);

                strSQL = "SELECT kpi.BGM_REQ_KPI_SEQ, kpi.BGM_REQ_KPI_NAME, kpi.BGM_REQ_KPI_DESCR, kpi.BGM_REQ_KPI_WEIGHT"
                        + ", kpi.BGM_KPI_TYPE_SEQ, kpi.SCORE_1, kpi.SCORE_2, kpi.SCORE_3, kpi.SCORE_4, kpi.SCORE_5"                        
                        + ", kpi.TARGET_TYPE, kpi.TARGET_Q1, kpi.TARGET_Q2, kpi.TARGET_Q3, kpi.TARGET_Q4"
                        + ", kpi.UNIT, mt.BGM_KPI_TYPE_NAME"
                    + " FROM BGM_REQUEST_KPI kpi"
                        + " INNER JOIN BGM_MAS_KPI_TYPE mt on kpi.BGM_KPI_TYPE_SEQ = mt.BGM_KPI_TYPE_SEQ"
                    + " WHERE 0=0"
                        + ((pj.Sequence > 0) ? " AND kpi.BGM_REQ_KPI_SEQ = @seq" : "")
                        + ((pjSeq > 0) ? " AND kpi.BGM_REQ_SEQ = @pjSeq" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    // ------------------------------ KPI TYPE ----------------------------------------
                    KPITypeInfo kpiTypeInfo = new KPITypeInfo();

                    kpiTypeInfo.Sequence = Convert.ToInt32(dr["bgm_kpi_type_seq"]);
                    kpiTypeInfo.Name = Convert.ToString(dr["bgm_kpi_type_name"]);


                  // -----------------------------------------------------------------------------------
                    ProjectKPIInfo req = new ProjectKPIInfo();
                    req.Sequence = Convert.ToInt32(dr["BGM_REQ_KPI_SEQ"]);
                    req.Name = Convert.ToString(dr["BGM_REQ_KPI_NAME"]);
                    req.Description = Convert.ToString(dr["BGM_REQ_KPI_DESCR"]);
                    req.Weight = Convert.ToInt32(dr["BGM_REQ_KPI_WEIGHT"]);
                    req.KPI_Type = kpiTypeInfo;
                    req.Score1 = Convert.ToInt32(dr["SCORE_1"]);
                    req.Score2 = Convert.ToInt32(dr["SCORE_2"]);
                    req.Score3 = Convert.ToInt32(dr["SCORE_3"]);
                    req.Score4 = Convert.ToInt32(dr["SCORE_4"]);
                    req.Score5 = Convert.ToInt32(dr["SCORE_5"]);
                    req.TargetType = Convert.ToInt32(dr["TARGET_TYPE"]);
                    req.Target_Quarter1 = Convert.ToInt32(dr["TARGET_Q1"]);
                    req.Target_Quarter2 = Convert.ToInt32(dr["TARGET_Q2"]);
                    req.Target_Quarter3 = Convert.ToInt32(dr["TARGET_Q3"]);
                    req.Target_Quarter4 = Convert.ToInt32(dr["TARGET_Q4"]);
                    req.Unit =  Convert.ToString(dr["UNIT"]);

                    pjInfo.Add(req);
                }

                //while (rdr.Read())
                //{
                //    // ------------------------------ KPI TYPE ----------------------------------------

                //    KPITypeInfo kpiTypeInfo = new KPITypeInfo();

                //    kpiTypeInfo.Sequence = rdr.GetInt32(4);
                //    kpiTypeInfo.Name = rdr.GetString(16);

                //    // -----------------------------------------------------------------------------------

                //    ProjectKPIInfo req = new ProjectKPIInfo();

                //    req.Sequence = rdr.GetInt32(0);
                //    req.Name = rdr.GetString(1);
                //    req.Description = (rdr.IsDBNull(2)) ? "" : rdr.GetString(2);
                //    req.Weight = rdr.GetDouble(3);
                //    req.KPI_Type = kpiTypeInfo;
                //    req.Score1 = rdr.GetDouble(5);
                //    req.Score2 = rdr.GetDouble(6);
                //    req.Score3 = rdr.GetDouble(7);
                //    req.Score4 = rdr.GetDouble(8);
                //    req.Score5 = rdr.GetDouble(9);
                //    req.Sequence = rdr.GetInt32(10);
                //    req.Target_Quarter1 = rdr.IsDBNull(11) ? (Nullable<double>)rdr.GetDouble(11) : 0;
                //    req.Target_Quarter2 = rdr.IsDBNull(12) ? (Nullable<double>)rdr.GetDouble(12) : 0;
                //    req.Target_Quarter3 = rdr.IsDBNull(13) ? (Nullable<double>)rdr.GetDouble(13) : 0;
                //    req.Target_Quarter4 = rdr.IsDBNull(14) ? (Nullable<double>)rdr.GetDouble(14) : 0;
                //    req.Unit = rdr.GetString(15);

                //    pjInfo.Add(req);
                //}

                return pjInfo;
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
